---
title: Google Cloud SQL for PostgreSQL
---

export const quartoRawHtml = [
  `
<table>
<colgroup>
<col style="width: 11%" />
<col style="width: 24%" />
<col style="width: 55%" />
<col style="width: 8%" />
</colgroup>
<thead>
<tr>
<th style="text-align: left;">Class</th>
<th style="text-align: left;">Package</th>
<th style="text-align: center;"><a href="https://python.langchain.com/docs/integrations/vectorstores/google_cloud_sql_pg/">PY support</a></th>
<th style="text-align: center;">Package latest</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left;">PostgresVectorStore</td>
<td style="text-align: left;"><a href="https://www.npmjs.com/package/@langchain/google-cloud-sql-pg"><code>@langchain/google-cloud-sql-pg</code></a></td>
<td style="text-align: center;">✅</td>
<td style="text-align: center;">0.0.1</td>
</tr>
</tbody>
</table>
`,
];

[Cloud SQL](https://cloud.google.com/sql) is a fully managed relational
database service that offers high performance, seamless integration, and
impressive scalability and offers database engines such as PostgreSQL.

This guide provides a quick overview of how to use Cloud SQL for
PostgreSQL to store vector embeddings with the `PostgresVectorStore`
class.

## Overview

### Integration details

<div dangerouslySetInnerHTML={{ __html: quartoRawHtml[0] }} />

### Before you begin

In order to use this package, you first need to go throught the
following steps:

1.  [Select or create a Cloud Platform
    project.](https://developers.google.com/workspace/guides/create-project)
2.  [Enable billing for your
    project.](https://cloud.google.com/billing/docs/how-to/modify-project#enable_billing_for_a_project)
3.  [Enable the Cloud SQL Admin
    API.](https://console.cloud.google.com/flows/enableapi?apiid=sqladmin.googleapis.com)
4.  [Setup
    Authentication.](https://cloud.google.com/docs/authentication)
5.  [Create a CloudSQL
    instance](https://cloud.google.com/sql/docs/postgres/connect-instance-auth-proxy#create-instance)
6.  [Create a CloudSQL
    database](https://cloud.google.com/sql/docs/postgres/create-manage-databases)
7.  [Add a user to the
    database](https://cloud.google.com/sql/docs/postgres/create-manage-users)

### Authentication

Authenticate locally to your Google Cloud account using the
`gcloud auth login` command.

### Set Your Google Cloud Project

Set your Google Cloud project ID to leverage Google Cloud resources
locally:

```python
gcloud config set project YOUR-PROJECT-ID
```

If you don’t know your project ID, try the following: \* Run
`gcloud config list`. \* Run `gcloud projects list`. \* See the support
page: [Locate the project
ID](https://support.google.com/googleapi/answer/7014113).

## Setting up a PostgresVectorStore instance

To use the PostgresVectorStore library, you’ll need to install the
`@langchain/google-cloud-sql-pg` package and then follow the steps
bellow.

First, you’ll need to log in to your Google Cloud account and set the
following environment variables based on your Google Cloud project;
these will be defined based on how you want to configure (fromInstance,
fromEngine, fromEngineArgs) your PostgresEngine instance :

```python
PROJECT_ID="your-project-id"
REGION="your-project-region" // example: "us-central1"
INSTANCE_NAME="your-instance"
DB_NAME="your-database-name"
DB_USER="your-database-user"
PASSWORD="your-database-password"
```

### Setting up an instance

To instantiate a PostgresVectorStore, you’ll first need to create a
database connection through the PostgresEngine, then initialize the
vector store table and finally call the `.initialize()` method to
instantiate the vector store.

```python
import {
  Column,
  PostgresEngine,
  PostgresEngineArgs,
  PostgresVectorStore,
  PostgresVectorStoreArgs,
  VectorStoreTableArgs,
} from "@langchain/google-cloud-sql-pg";
import { SyntheticEmbeddings } from "@langchain/core/utils/testing"; // This is used as an Embedding service
import * as dotenv from "dotenv";

dotenv.config();

const peArgs: PostgresEngineArgs = {
  user: process.env.DB_USER ?? "",
  password: process.env.PASSWORD ?? "",
};

// PostgresEngine instantiation
const engine: PostgresEngine = await PostgresEngine.fromInstance(
  process.env.PROJECT_ID ?? "",
  process.env.REGION ?? "",
  process.env.INSTANCE_NAME ?? "",
  process.env.DB_NAME ?? "",
  peArgs
);

const vectorStoreArgs: VectorStoreTableArgs = {
  metadataColumns: [new Column("page", "TEXT"), new Column("source", "TEXT")],
};

// Vector store table initilization
await engine.initVectorstoreTable("my_vector_store_table", 768, vectorStoreArgs);
const embeddingService = new SyntheticEmbeddings({ vectorSize: 768 });

const pvectorArgs: PostgresVectorStoreArgs = {
  metadataColumns: ["page", "source"],
};

// PostgresVectorStore instantiation
const vectorStore = await PostgresVectorStore.initialize(
  engine,
  embeddingService,
  "my_vector_store_table",
  pvectorArgs
);

```

## Manage Vector Store

### Add Documents to vector store

To add Documents to the vector store, you would be able to it by passing
or not the ids

```python
import { v4 as uuidv4 } from "uuid";
import type { Document } from "@langchain/core/documents";

const document1: Document = {
  pageContent: "The powerhouse of the cell is the mitochondria",
  metadata: { page: 0, source: "https://example.com" },
};

const document2: Document = {
  pageContent: "Buildings are made out of brick",
  metadata: { page: 1, source: "https://example.com" },
};

const document3: Document = {
  pageContent: "Mitochondria are made out of lipids",
  metadata: { page: 2, source: "https://example.com" },
};

const document4: Document = {
  pageContent: "The 2024 Olympics are in Paris",
  metadata: { page: 3, source: "https://example.com" },
};

const documents = [document1, document2, document3, document4];

const ids = [uuidv4(), uuidv4(), uuidv4(), uuidv4()];

await vectorStore.addDocuments(documents, { ids: ids });

```

### Delete Documents from vector store

You can delete one or more Documents from the vector store by passing
the arrays of ids to be deleted:

```python
// deleting a document
const id1 = ids[0];
await vectorStore.delete({ ids: [id1] });

// deleting more than one document
await vectorStore.delete({ ids: ids });

```

## Search for documents

Once your vector store has been created and the relevant documents have
been added you will most likely wish to query it during the running of
your chain or agent.

### Query directly

Performing a simple similarity search can be done as follows:

```python
const filter = `"source" = "https://example.com"`;

const results = await vectorStore.similaritySearch("biology", 2, filter);

for (const doc of results) {
  console.log(`* ${doc.pageContent} [${JSON.stringify(doc.metadata, null)}]`);
}

```

If you want to execute a similarity search and receive the corresponding
scores you can run:

```python
const filter = `"source" = "https://example.com"`;
const resultsWithScores = await vectorStore.similaritySearchWithScore(
  "biology",
  2,
  filter
);

for (const [doc, score] of resultsWithScores) {
  console.log(
    `* [SIM=${score.toFixed(3)}] ${doc.pageContent} [${JSON.stringify(doc.metadata)}]`
  );
}

```

### Query by using the max marginal relevance search

The Maximal marginal relevance optimizes for similarity to the query and
diversity among selected documents.

```python
const options = {
  k: 4,
  filter: `"source" = 'https://example.com'`,
};

const results = await vectorStoreInstance.maxMarginalRelevanceSearch("biology", options);

for (const doc of results) {
  console.log(`* ${doc.pageContent} [${JSON.stringify(doc.metadata, null)}]`);
}

```
